package sh.wn36.vehiclemanage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import sh.wn36.vehiclemanage.entity.RepairRecord;
import sh.wn36.vehiclemanage.xzh.RepairInfo;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author xk
 * @since 2021-04-26
 */
public interface RepairRecordMapper extends BaseMapper<RepairRecord> {

    @Select("select r.*,c.car_no,cu.unit_name from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID \n")
    List<RepairInfo> getList();

    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID\n" +
            "LEFT JOIN q_users q on q.id = r.OPERATOR \n")
    IPage<RepairInfo> ListByPage(Page<RepairInfo> page);

    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r" +
            "        LEFT JOIN car c on r.CAR_ID = c.id" +
            "        LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID" +
            "        LEFT JOIN q_users q on q.id = r.OPERATOR " +
            "        where c.car_no like concat(\"%\",#{text},\"%\")")
    IPage<RepairInfo> ListByCarNo(Page<RepairInfo> page, @Param("text") String text);

    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID\n" +
            "LEFT JOIN q_users q on q.id = r.OPERATOR \n" +
            "where cu.unit_name like concat(\"%\",#{text},\"%\")")
    IPage<RepairInfo> ListByUnitName(Page<RepairInfo> page, @Param("text") String text);


    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID\n" +
            "LEFT JOIN q_users q on q.id = r.OPERATOR \n" +
            "where q.rname like concat(\"%\",#{text},\"%\")")
    IPage<RepairInfo> ListByRname(Page<RepairInfo> page, @Param("text") String text);

    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID\n" +
            "LEFT JOIN q_users q on q.id = r.OPERATOR \n" +
            "where r.id = #{id}\n")
    RepairInfo getById(@Param("id") Integer id);

    @Select("select r.*,c.car_no,cu.unit_name,q.rname from repair_record r\n" +
            "LEFT JOIN car c on r.CAR_ID = c.id\n" +
            "LEFT JOIN current_unit cu on cu.id = r.REPAIR_DEPOT_ID\n" +
            "LEFT JOIN q_users q on q.id = r.OPERATOR \n" +
            "where r.id = #{id}")
    RepairInfo getDetailById(@Param("id") Integer id);

    @Select("SELECT\n" +
            "c.car_no ,\n" +
            "IFNULL(SUM(rr.REPAIR_COST ),0) AS car_expenses,\n" +
            "\"维修记录表\" AS '数据来源'\n" +
            "FROM\n" +
            "repair_record rr\n" +
            "LEFT JOIN car c ON rr.CAR_ID = c.id\n" +
            "GROUP BY c.car_no\n" +
            "HAVING car_expenses!=0;")
    List<Map<String, Object>> getTable();

    @Select("SELECT\n" +
            "c.car_no ,\n" +
            "IFNULL(SUM(rr.REPAIR_COST ),0) AS car_expenses,\n" +
            "\"维修记录表\" AS '数据来源'\n" +
            "FROM\n" +
            "repair_record rr\n" +
            "LEFT JOIN car c ON rr.CAR_ID = c.id\n" +
            "where c.id = #{id}\n" +
            "GROUP BY c.car_no\n" +
            "HAVING car_expenses!=0;")
    List<Map<String, Object>> getTableByCarId( @Param("id")Integer id);
}
